#!/usr/bin/evn python
# -*- encoding: utf-8 -*-

import xlutils.copy
from plan_util import *


class Merge(Util):
    def __init__(self, host='', username='', password='', media='', pthost='', nowFilePath="", charset='utf8'):
        self.host = host
        self.username = username
        self.password = password
        self.charset = charset
        self.media = media
        self.pthost = pthost
        self.nowFilePath = nowFilePath

        self.dbconn = None
        self.cursor = None
        self.flag = True

        self.users = {}
        self.SELECT_COMMUNION_USER = "SELECT userId, username FROM  communion.user "
        self.filePath = tempfile.gettempdir()
        self.errPath = self.filePath + "/err.log"

    def getUser(self, sql):

        self.getDBCursor(self.host, self.username, self.password, self.charset)

        self.cursor.execute(sql)

        _users = self.cursor.fetchall()

        self.closeDBConnect()

        for i in xrange(len(_users)):
            _user = _users[i]

            self.users[_user[0]] = {"name": _user[1]}

    def ceatHead(self, ymd, ymd2, months):

        startYear = ymd[0:4]
        startMonth = ymd[4:6]

        endYear = ymd2[0:4]
        endMonth = ymd2[4:6]

        param = self.createWorkbook(ymd)

        workbook = param[0]
        sheet = param[1]

        tableHead = [u"工号", u"姓名", u"班次"]
        self.createTableHead(sheet, tableHead)

        startmonth = int(ymd[4:6])

        start = 3
        steps = 34
        countm = 0
        while (countm < months):
            days = 1
            for field in range(start, steps):
                self.writeWorkbook(sheet, 0, field, u"%s月%s号" % (startmonth, days))
                days = days + 1

            start = steps
            steps = steps + 31
            startmonth = startmonth + 1
            countm = countm + 1

        if months != 1:
            fileName = u"/%s年%s月~%s年%s月合并.xls" % (startYear, startMonth, endYear, endMonth)
        else:
            fileName = u"/%s年%s月合并.xls" % (startYear, startMonth)

        spath = self.filePath + fileName

        self.saveWorkbook(spath, workbook)

        return spath

    def controlTime(self, startTime, endTime, num=31):

        if type(startTime) != datetime.datetime:

            if startTime.find(u"/") != -1:
                startTime = startTime.replace("/", "-")
                endTime = endTime.replace("/", "-")

            if len(startTime)==10:
                startTime = "%s 00:00:00" % startTime
                endTime = "%s 23:59:59" % endTime

            startTime = datetime.datetime.strptime(startTime, '%Y-%m-%d %H:%M:%S')
            endTime = datetime.datetime.strptime(endTime, '%Y-%m-%d %H:%M:%S')

        if endTime - startTime <= datetime.timedelta(days=num):
            return True
        else:
            return False

    def main(self, pid, times):
        try:
            _times = times.split("/")
            ymd = _times[0]
            ymd2 = _times[1]
            print ymd, ymd2
            self.getUser(self.SELECT_COMMUNION_USER)
            users = self.users

            year = ymd[0:4]
            month = ymd[4:86]

            startMonth = int(ymd[4:6])
            endMonth = int(ymd2[4:6])
            months = endMonth - startMonth + 1
            spath = self.ceatHead(ymd, ymd2, months)

            self.getDBCursor(self.host, self.username, self.password, self.charset)

            all = 100

            schedule = 0.2 * all

            paln = {
                u"plan.schedule": int(schedule),
                u"plan.status": 4
            }
            self.updataSchedule(self.pthost, pid, paln)

            countm = 0
            count = 0
            month = int(month)

            schedules = 70 / months
            countSchedule = schedule
            while (countm < months):

                strMontht = u"%s" % month
                s = strMontht.zfill(2)

                path = self.nowFilePath + u"/attendance/%s%s/" % (year, s)
                _path = self.nowFilePath + u"/attendance/%s%s/%s"

                for filename in os.listdir(path):

                    day = int(filename[4:6])
                    _paths = _path % (year, s, filename)

                    _param = self.openWorkbook(_paths)
                    # book = _param[0]
                    table = _param[1]

                    data0 = table.col_values(0)
                    data1 = table.col_values(day + 2)

                    params = self.openWorkbook(spath)
                    allBook = params[0]
                    allBookTable = params[1]

                    allBookTCol = allBookTable.col_values(0)

                    updataBook = xlutils.copy.copy(allBook)
                    sheet = updataBook.get_sheet(0)
                    crow = len(allBookTCol)

                    for i in range(0, len(data0), 2):
                        if i == 0:
                            continue
                        userId = data0[i - 1]

                        if userId not in allBookTCol:

                            if users.has_key(userId):
                                userName = users[userId]["name"]
                            else:
                                userName = ""

                            self.writeWorkbook(sheet, crow, 0, u'%s' % userId)
                            self.writeWorkbook(sheet, crow, 1, u'%s' % userName)

                            self.writeWorkbook(sheet, crow, 2, u'上班')
                            self.writeWorkbook(sheet, crow + 1, 2, u'下班')

                            cssNum = 22
                            shangban = data1[i - 1]
                            if shangban != u"正常":
                                cssNum = 5

                            self.writeWorkbook(sheet, crow, day + 2 + count, u'%s' % shangban, cssNum)

                            xiaban = data1[i]
                            if xiaban != u"正常":
                                cssNum = 5

                            self.writeWorkbook(sheet, crow + 1, day + 2 + count, u'%s' % xiaban, cssNum)
                            crow = crow + 2
                        else:

                            index = allBookTCol.index(userId)

                            cssNum = 22
                            shangban = data1[i - 1]
                            if shangban != u"正常":
                                cssNum = 5

                            self.writeWorkbook(sheet, index, day + 2 + count, u'%s' % shangban, cssNum)

                            xiaban = data1[i]
                            if xiaban != u"正常":
                                cssNum = 5

                            self.writeWorkbook(sheet, index + 1, day + 2 + count, u'%s' % xiaban, cssNum)

                    self.saveWorkbook(spath, updataBook)

                countSchedule = countSchedule + schedules
                schedule = countSchedule
                plan = {
                    u"plan.schedule": int(schedule)
                }
                self.updataSchedule(self.pthost, pid, plan)

                count = count + 31
                month = month + 1
                countm = countm + 1

            url = self.media + u"uploadOther.do"
            resultfilepath = self.uploudFile(spath, url)

            self.delFile(spath)

            paln = {
                u"plan.resultFilepath": resultfilepath
            }

            self.updataSchedule(self.pthost, pid, paln)

        except Exception as e:
            self.flag = False
            self.errCall(pid, self.pthost, self.media, e)

        finally:

            self.modelEnd(pid, self.pthost, self.media, self.flag)

            sys.exit(0)


if __name__ == '__main__':
    """
     1.脚本说明:
        配合script_attendance.py 合并指定月份的考勤文件,导出一个新的当月合并文件
        新建合并文件,获得当月路径下所有的文件后,每次向合并文件追加当月每天的数据
        上传合并文件到服务器中后删除临时文件
        合并文件路径:在/home/fanglin/cronbin/attendance/merge/文件夹下
        状态:正常--灰色
             异常--黄色
     3.例:
        1.  
        Python plan_merge_personel_attendance.py <pid><手动输入时间>手动指定时间
               plan_merge_personel_attendance.py 142   2018/05/01-2018/06/01 
        2.
           任务计划中参数请填写
           例如: 
            1.指定日期: 2018/05/14-2018/06/14
            
     3.导出结果:
        员工号    姓名      1号      2号        ....
        FL-0001  王xx    正常,迟到  正常,正常     ....
        FL-0002  李xx    正常,正常  正常,正常     ....
     4. 异常处理
         在当出现异常时,跟新状态为失败将异常记录到log文件中,最后上传,并删除临时文件
    """
    modernFilePath = os.path.realpath(__file__)
    filepath = os.path.dirname(modernFilePath)
    configPath = "%s/%s" % (os.path.dirname(modernFilePath), "config.json")

    config = json.load(open(configPath))
    dbconf = config.get("database")
    merge = Merge(dbconf.get("host"), dbconf.get("username"), dbconf.get("password"), config.get("media"), config.get("pthost") ,filepath)

    pid = sys.argv[1]
    args = sys.argv[2]

    times = args.split("-")
    startTime = times[0]
    endTime = times[1]

    falgs = merge.controlTime(startTime, endTime)
    if falgs == False:
        raise RuntimeError(u'时间应在1-31内')
    timeRange = times[0][0:4] + times[0][5:7] + "/" + times[1][0:4] + times[1][5:7]

    merge.main(pid, timeRange)
